<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>数据处理之增删改 | 知识库</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/noteslibrary/img/favicon.ico">
    <meta name="description" content="Java后端技术博客,专注Java后端学习与总结。HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github等技术文章。">
    <meta name="keywords" content="后端博客,个人技术博客,后端,后端开发,后端框架,后端面试题,技术文档,学习,面试,HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/noteslibrary/assets/css/0.styles.2500ff6d.css" as="style"><link rel="preload" href="/noteslibrary/assets/js/app.dc0c3d24.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/2.a664539c.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/98.e9a01f98.js" as="script"><link rel="prefetch" href="/noteslibrary/assets/js/10.bab1a8b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/100.245d2d1d.js"><link rel="prefetch" href="/noteslibrary/assets/js/101.9d1c6c13.js"><link rel="prefetch" href="/noteslibrary/assets/js/102.d187686f.js"><link rel="prefetch" href="/noteslibrary/assets/js/103.b0e7acf6.js"><link rel="prefetch" href="/noteslibrary/assets/js/104.895c9e53.js"><link rel="prefetch" href="/noteslibrary/assets/js/105.235036be.js"><link rel="prefetch" href="/noteslibrary/assets/js/106.17a71ae1.js"><link rel="prefetch" href="/noteslibrary/assets/js/107.256ceb5d.js"><link rel="prefetch" href="/noteslibrary/assets/js/108.57832d85.js"><link rel="prefetch" href="/noteslibrary/assets/js/109.f8b1d1c7.js"><link rel="prefetch" href="/noteslibrary/assets/js/11.2c69801a.js"><link rel="prefetch" href="/noteslibrary/assets/js/110.f3ce6a68.js"><link rel="prefetch" href="/noteslibrary/assets/js/111.bdfd28f4.js"><link rel="prefetch" href="/noteslibrary/assets/js/112.878f599c.js"><link rel="prefetch" href="/noteslibrary/assets/js/113.0efa07cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/114.c7aba251.js"><link rel="prefetch" href="/noteslibrary/assets/js/115.8b2e628d.js"><link rel="prefetch" href="/noteslibrary/assets/js/116.3392d8ba.js"><link rel="prefetch" href="/noteslibrary/assets/js/117.310538d0.js"><link rel="prefetch" href="/noteslibrary/assets/js/118.81eb015b.js"><link rel="prefetch" href="/noteslibrary/assets/js/12.e77f5a18.js"><link rel="prefetch" href="/noteslibrary/assets/js/13.d24d0054.js"><link rel="prefetch" href="/noteslibrary/assets/js/14.f29d4d33.js"><link rel="prefetch" href="/noteslibrary/assets/js/15.8694e081.js"><link rel="prefetch" href="/noteslibrary/assets/js/16.03ffb143.js"><link rel="prefetch" href="/noteslibrary/assets/js/17.83edd7b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/18.58ca4447.js"><link rel="prefetch" href="/noteslibrary/assets/js/19.e136d5c2.js"><link rel="prefetch" href="/noteslibrary/assets/js/20.fd3961b6.js"><link rel="prefetch" href="/noteslibrary/assets/js/21.207406c6.js"><link rel="prefetch" href="/noteslibrary/assets/js/22.b8738ce2.js"><link rel="prefetch" href="/noteslibrary/assets/js/23.33e4529d.js"><link rel="prefetch" href="/noteslibrary/assets/js/24.ab5493c5.js"><link rel="prefetch" href="/noteslibrary/assets/js/25.2506ce48.js"><link rel="prefetch" href="/noteslibrary/assets/js/26.7e6a9c14.js"><link rel="prefetch" href="/noteslibrary/assets/js/27.e7b4e92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/28.7ad46ba6.js"><link rel="prefetch" href="/noteslibrary/assets/js/29.81666f41.js"><link rel="prefetch" href="/noteslibrary/assets/js/3.8f13cd17.js"><link rel="prefetch" href="/noteslibrary/assets/js/30.07ada09b.js"><link rel="prefetch" href="/noteslibrary/assets/js/31.f271c8cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/32.2350914c.js"><link rel="prefetch" href="/noteslibrary/assets/js/33.9d7bfb77.js"><link rel="prefetch" href="/noteslibrary/assets/js/34.6fcf6f6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/35.0c3a88fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/36.574ca92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/37.72e38074.js"><link rel="prefetch" href="/noteslibrary/assets/js/38.16d408fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/39.63abc4a8.js"><link rel="prefetch" href="/noteslibrary/assets/js/4.3e68fc94.js"><link rel="prefetch" href="/noteslibrary/assets/js/40.ca2bdd48.js"><link rel="prefetch" href="/noteslibrary/assets/js/41.f82c80ec.js"><link rel="prefetch" href="/noteslibrary/assets/js/42.d1b8f579.js"><link rel="prefetch" href="/noteslibrary/assets/js/43.d3fd6260.js"><link rel="prefetch" href="/noteslibrary/assets/js/44.491f9afa.js"><link rel="prefetch" href="/noteslibrary/assets/js/45.1ee0d084.js"><link rel="prefetch" href="/noteslibrary/assets/js/46.a3e0be1f.js"><link rel="prefetch" href="/noteslibrary/assets/js/47.647908d1.js"><link rel="prefetch" href="/noteslibrary/assets/js/48.9658b8b0.js"><link rel="prefetch" href="/noteslibrary/assets/js/49.8f4b8327.js"><link rel="prefetch" href="/noteslibrary/assets/js/5.a0a97ccd.js"><link rel="prefetch" href="/noteslibrary/assets/js/50.f46d1433.js"><link rel="prefetch" href="/noteslibrary/assets/js/51.f1b784a0.js"><link rel="prefetch" href="/noteslibrary/assets/js/52.cce45956.js"><link rel="prefetch" href="/noteslibrary/assets/js/53.d66dabe3.js"><link rel="prefetch" href="/noteslibrary/assets/js/54.384b864b.js"><link rel="prefetch" href="/noteslibrary/assets/js/55.47ed19f2.js"><link rel="prefetch" href="/noteslibrary/assets/js/56.193cd456.js"><link rel="prefetch" href="/noteslibrary/assets/js/57.e6ea1f8c.js"><link rel="prefetch" href="/noteslibrary/assets/js/58.97fd2330.js"><link rel="prefetch" href="/noteslibrary/assets/js/59.b0c3d9ea.js"><link rel="prefetch" href="/noteslibrary/assets/js/6.50cbd75f.js"><link rel="prefetch" href="/noteslibrary/assets/js/60.d01d0651.js"><link rel="prefetch" href="/noteslibrary/assets/js/61.385e9bae.js"><link rel="prefetch" href="/noteslibrary/assets/js/62.a93fa4c8.js"><link rel="prefetch" href="/noteslibrary/assets/js/63.f72a2142.js"><link rel="prefetch" href="/noteslibrary/assets/js/64.3bf0b024.js"><link rel="prefetch" href="/noteslibrary/assets/js/65.cb1cb3bb.js"><link rel="prefetch" href="/noteslibrary/assets/js/66.4c9ff8cd.js"><link rel="prefetch" href="/noteslibrary/assets/js/67.2fc17900.js"><link rel="prefetch" href="/noteslibrary/assets/js/68.fd3ee410.js"><link rel="prefetch" href="/noteslibrary/assets/js/69.682be05d.js"><link rel="prefetch" href="/noteslibrary/assets/js/7.80203dee.js"><link rel="prefetch" href="/noteslibrary/assets/js/70.29428a45.js"><link rel="prefetch" href="/noteslibrary/assets/js/71.aff6ef6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/72.fe7572e0.js"><link rel="prefetch" href="/noteslibrary/assets/js/73.e52bc1a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/74.5e72ee84.js"><link rel="prefetch" href="/noteslibrary/assets/js/75.5a5bba64.js"><link rel="prefetch" href="/noteslibrary/assets/js/76.70d6ff7c.js"><link rel="prefetch" href="/noteslibrary/assets/js/77.1db1c302.js"><link rel="prefetch" href="/noteslibrary/assets/js/78.137c92e8.js"><link rel="prefetch" href="/noteslibrary/assets/js/79.8455d34b.js"><link rel="prefetch" href="/noteslibrary/assets/js/8.9e13e493.js"><link rel="prefetch" href="/noteslibrary/assets/js/80.127f3a4e.js"><link rel="prefetch" href="/noteslibrary/assets/js/81.191d3614.js"><link rel="prefetch" href="/noteslibrary/assets/js/82.6ae31745.js"><link rel="prefetch" href="/noteslibrary/assets/js/83.640cd3d7.js"><link rel="prefetch" href="/noteslibrary/assets/js/84.22c036e2.js"><link rel="prefetch" href="/noteslibrary/assets/js/85.692cd496.js"><link rel="prefetch" href="/noteslibrary/assets/js/86.793e38a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/87.eb27f1d6.js"><link rel="prefetch" href="/noteslibrary/assets/js/88.6d48c75e.js"><link rel="prefetch" href="/noteslibrary/assets/js/89.a798bacb.js"><link rel="prefetch" href="/noteslibrary/assets/js/9.7e864ff7.js"><link rel="prefetch" href="/noteslibrary/assets/js/90.e814aadc.js"><link rel="prefetch" href="/noteslibrary/assets/js/91.5c132772.js"><link rel="prefetch" href="/noteslibrary/assets/js/92.d0d1984d.js"><link rel="prefetch" href="/noteslibrary/assets/js/93.bbc81ca6.js"><link rel="prefetch" href="/noteslibrary/assets/js/94.242fbc29.js"><link rel="prefetch" href="/noteslibrary/assets/js/95.f7232d68.js"><link rel="prefetch" href="/noteslibrary/assets/js/96.440e6bbe.js"><link rel="prefetch" href="/noteslibrary/assets/js/97.a8ad3aa8.js"><link rel="prefetch" href="/noteslibrary/assets/js/99.e024f354.js">
    <link rel="stylesheet" href="/noteslibrary/assets/css/0.styles.2500ff6d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/noteslibrary/" class="home-link router-link-active"><img src="/noteslibrary/img/EB-logo.png" alt="知识库" class="logo"> <span class="site-name can-hide">知识库</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/a20c5b/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a20c5b/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a20c5b/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/a20c5b/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a20c5b/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a20c5b/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/noteslibrary/pages/32e9fd/" class="sidebar-link">写在前面</a></li><li><a href="/noteslibrary/pages/cfda94/" class="sidebar-link">数据库概述</a></li><li><a href="/noteslibrary/pages/497bcb/" class="sidebar-link">MySQL环境搭建</a></li><li><a href="/noteslibrary/pages/9c9f3a/" class="sidebar-link">基本的SELECT语句</a></li><li><a href="/noteslibrary/pages/b59830/" class="sidebar-link">运算符</a></li><li><a href="/noteslibrary/pages/6fef5b/" class="sidebar-link">排序与分页</a></li><li><a href="/noteslibrary/pages/3a50a8/" class="sidebar-link">多表查询</a></li><li><a href="/noteslibrary/pages/8de5b4/" class="sidebar-link">单行函数</a></li><li><a href="/noteslibrary/pages/2dfe6d/" class="sidebar-link">聚合函数</a></li><li><a href="/noteslibrary/pages/3ea5ac/" class="sidebar-link">子查询</a></li><li><a href="/noteslibrary/pages/70a90b/" class="sidebar-link">创建和管理表</a></li><li><a href="/noteslibrary/pages/a20c5b/" aria-current="page" class="active sidebar-link">数据处理之增删改</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/a20c5b/#_1-插入数据" class="sidebar-link">1. 插入数据</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/a20c5b/#_1-1-实际问题" class="sidebar-link">1.1 实际问题</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/a20c5b/#_1-2-方式1-values的方式添加" class="sidebar-link">1.2 方式1：VALUES的方式添加</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/a20c5b/#_1-3-方式2-将查询结果插入到表中" class="sidebar-link">1.3 方式2：将查询结果插入到表中</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/a20c5b/#_2-更新数据" class="sidebar-link">2. 更新数据</a></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/a20c5b/#_3-删除数据" class="sidebar-link">3. 删除数据</a></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/a20c5b/#_4-mysql8新特性-计算列" class="sidebar-link">4. MySQL8新特性：计算列</a></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/a20c5b/#_5-综合案例" class="sidebar-link">5. 综合案例</a></li></ul></li><li><a href="/noteslibrary/pages/9515c0/" class="sidebar-link">MySQL数据类型精讲</a></li><li><a href="/noteslibrary/pages/98e681/" class="sidebar-link">约束</a></li><li><a href="/noteslibrary/pages/4535e0/" class="sidebar-link">视图</a></li><li><a href="/noteslibrary/pages/e5e604/" class="sidebar-link">存储过程与函数</a></li><li><a href="/noteslibrary/pages/f68b46/" class="sidebar-link">变量与流程控制与游标</a></li><li><a href="/noteslibrary/pages/fe941e/" class="sidebar-link">触发器</a></li><li><a href="/noteslibrary/pages/efad7b/" class="sidebar-link">MySQL8其它新特性</a></li></ul></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/noteslibrary/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/noteslibrary/database/#数据库" data-v-06225672>数据库</a></li><li data-v-06225672><a href="/noteslibrary/database/#MySQL笔记" data-v-06225672>MySQL笔记</a></li></ul> <div class="info" data-v-06225672><!----> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-04-05</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">数据处理之增删改<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="数据处理之增删改"><a href="#数据处理之增删改" class="header-anchor">#</a> 数据处理之增删改</h1> <h2 id="_1-插入数据"><a href="#_1-插入数据" class="header-anchor">#</a> 1. 插入数据</h2> <h3 id="_1-1-实际问题"><a href="#_1-1-实际问题" class="header-anchor">#</a> 1.1 实际问题</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929549.png" alt="image-20220610121520988"></p> <p>解决方式：使用 INSERT 语句向表中插入数据。</p> <h3 id="_1-2-方式1-values的方式添加"><a href="#_1-2-方式1-values的方式添加" class="header-anchor">#</a> 1.2 方式1：VALUES的方式添加</h3> <p>使用这种语法一次只能向表中插入<code>一条</code>数据。
<strong>情况1：为表的所有字段按默认顺序插入数据</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> 表名
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span>value1<span class="token punctuation">,</span>value2<span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> departments
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">70</span><span class="token punctuation">,</span> <span class="token string">'Pub'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">,</span> <span class="token number">1700</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> departments
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token string">'Finance'</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>情况2：为表的指定字段插入数据</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> 表名<span class="token punctuation">(</span>column1 <span class="token punctuation">[</span><span class="token punctuation">,</span> column2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> columnn<span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span>value1 <span class="token punctuation">[</span><span class="token punctuation">,</span>value2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> valuen<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。</p> <p>在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与
column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。</p> <p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> departments<span class="token punctuation">(</span>department_id<span class="token punctuation">,</span> department_name<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">80</span><span class="token punctuation">,</span> <span class="token string">'IT'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>情况3：同时插入多条记录</strong></p> <p>INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> table_name
<span class="token keyword">VALUES</span>
<span class="token punctuation">(</span>value1 <span class="token punctuation">[</span><span class="token punctuation">,</span>value2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> valuen<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span>value1 <span class="token punctuation">[</span><span class="token punctuation">,</span>value2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> valuen<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
……
<span class="token punctuation">(</span>value1 <span class="token punctuation">[</span><span class="token punctuation">,</span>value2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> valuen<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>或者</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> table_name<span class="token punctuation">(</span>column1 <span class="token punctuation">[</span><span class="token punctuation">,</span> column2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> columnn<span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token keyword">VALUES</span>
<span class="token punctuation">(</span>value1 <span class="token punctuation">[</span><span class="token punctuation">,</span>value2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> valuen<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span>value1 <span class="token punctuation">[</span><span class="token punctuation">,</span>value2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> valuen<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
……
<span class="token punctuation">(</span>value1 <span class="token punctuation">[</span><span class="token punctuation">,</span>value2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> valuen<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>emp_id<span class="token punctuation">,</span>emp_name<span class="token punctuation">)</span>
 <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'shkstart'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
 <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span><span class="token string">'atguigu'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
 <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token punctuation">(</span><span class="token number">1003</span><span class="token punctuation">,</span><span class="token string">'Tom'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">3</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
Records: <span class="token number">3</span> Duplicates: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：</p> <p>●　Records：表明插入的记录条数。</p> <p>●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。</p> <p>●　Warnings：表明有问题的数据值，例如发生数据类型转换。</p> <blockquote><p>一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句
在处理过程中 效率更高 。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句
快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。</p></blockquote> <p><strong>小结</strong>：</p> <ul><li><code>VALUES</code> 也可以写成 <code>VALUE</code> ，但是VALUES是标准写法。</li> <li>字符和日期型数据应包含在单引号中。</li></ul> <h3 id="_1-3-方式2-将查询结果插入到表中"><a href="#_1-3-方式2-将查询结果插入到表中" class="header-anchor">#</a> 1.3 方式2：将查询结果插入到表中</h3> <p>INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。
基本语法格式如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> 目标表名
<span class="token punctuation">(</span>tar_column1 <span class="token punctuation">[</span><span class="token punctuation">,</span> tar_column2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> tar_columnn<span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token keyword">SELECT</span>
<span class="token punctuation">(</span>src_column1 <span class="token punctuation">[</span><span class="token punctuation">,</span> src_column2<span class="token punctuation">,</span> …<span class="token punctuation">,</span> src_columnn<span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span> 源表名
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> condition<span class="token punctuation">]</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><ul><li>在 INSERT 语句中加入子查询。</li> <li><strong>不必书写 VALUES 子句。</strong></li> <li>子查询中的值列表应与 INSERT 子句中的列名对应。</li></ul> <p>举例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp2
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> <span class="token number">90</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> sales_reps<span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> salary<span class="token punctuation">,</span> commission_pct<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> last_name<span class="token punctuation">,</span> salary<span class="token punctuation">,</span> commission_pct
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span> job_id <span class="token operator">LIKE</span> <span class="token string">'%REP%'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h2 id="_2-更新数据"><a href="#_2-更新数据" class="header-anchor">#</a> 2. 更新数据</h2> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929550.png" alt="image-20220610121854609"></p> <ul><li>使用 UPDATE 语句更新数据。语法如下：</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> table_name
<span class="token keyword">SET</span> column1<span class="token operator">=</span>value1<span class="token punctuation">,</span> column2<span class="token operator">=</span>value2<span class="token punctuation">,</span> … <span class="token punctuation">,</span> <span class="token keyword">column</span><span class="token operator">=</span>valuen
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> condition<span class="token punctuation">]</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li>可以一次更新<code>多条</code>数据。</li> <li>如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;</li> <li>使用 WHERE 子句指定需要更新的数据。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> employees
<span class="token keyword">SET</span>  department_id <span class="token operator">=</span> <span class="token number">70</span>
<span class="token keyword">WHERE</span> employee_id <span class="token operator">=</span> <span class="token number">113</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li>如果省略 WHERE 子句，则表中的所有数据都将被更新。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> copy_emp
<span class="token keyword">SET</span>  department_id <span class="token operator">=</span> <span class="token number">110</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><ul><li><strong>更新中的数据完整性错误</strong></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> employees
<span class="token keyword">SET</span>  department_id <span class="token operator">=</span> <span class="token number">55</span>
<span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> <span class="token number">110</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929551.png" alt="image-20220610121950705"></p> <blockquote><p>说明：不存在 55 号部门</p></blockquote> <h2 id="_3-删除数据"><a href="#_3-删除数据" class="header-anchor">#</a> 3. 删除数据</h2> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929552.png" alt="image-20220610122009034"></p> <ul><li>使用 DEETE 语句从表中删除数据</li></ul> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929553.png" alt="image-20220610122019589"></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> table_name <span class="token punctuation">[</span><span class="token keyword">WHERE</span> <span class="token operator">&lt;</span>condition<span class="token operator">&gt;</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。</p> <ul><li>使用 WHERE 子句删除指定的记录。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> departments
<span class="token keyword">WHERE</span> department_name <span class="token operator">=</span> <span class="token string">'Finance'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><ul><li>如果省略 WHERE 子句，则表中的全部数据将被删除</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> copy_emp<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>删除中的数据完整性错误</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> departments
<span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> <span class="token number">60</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929554.png" alt="image-20220610122121101"></p> <blockquote><p>说明：You cannot delete a row that contains a primary key that is used as a foreign key in another table.</p></blockquote> <h2 id="_4-mysql8新特性-计算列"><a href="#_4-mysql8新特性-计算列" class="header-anchor">#</a> 4. MySQL8新特性：计算列</h2> <p>什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。</p> <p>在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。</p> <p>举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。 首先创建测试表tb1，语句如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tb1<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span><span class="token punctuation">,</span>
    a <span class="token keyword">INT</span><span class="token punctuation">,</span>
    b <span class="token keyword">INT</span><span class="token punctuation">,</span>
    c <span class="token keyword">INT</span> GENERATED ALWAYS <span class="token keyword">AS</span> <span class="token punctuation">(</span>a <span class="token operator">+</span> b<span class="token punctuation">)</span> VIRTUAL
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>插入演示数据，语句如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tb1<span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">,</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>查询数据表tb1中的数据，结果如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tb1<span class="token punctuation">;</span>
<span class="token operator">+</span><span class="token comment">------+------+------+------+</span>
<span class="token operator">|</span> id  <span class="token operator">|</span> a  <span class="token operator">|</span> b  <span class="token operator">|</span> c  <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+------+------+------+</span>
<span class="token operator">|</span> <span class="token boolean">NULL</span> <span class="token operator">|</span>  <span class="token number">100</span> <span class="token operator">|</span>  <span class="token number">200</span> <span class="token operator">|</span>  <span class="token number">300</span> <span class="token operator">|</span>
<span class="token operator">+</span><span class="token comment">------+------+------+------+</span>
<span class="token number">1</span> <span class="token keyword">row</span> <span class="token operator">in</span> <span class="token keyword">set</span> <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>更新数据中的数据，语句如下：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>mysql<span class="token operator">&gt;</span> <span class="token keyword">UPDATE</span> tb1 <span class="token keyword">SET</span> a <span class="token operator">=</span> <span class="token number">500</span><span class="token punctuation">;</span>
Query OK<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token keyword">rows</span> affected <span class="token punctuation">(</span><span class="token number">0.00</span> sec<span class="token punctuation">)</span>
<span class="token keyword">Rows</span> <span class="token keyword">matched</span>: <span class="token number">1</span>  Changed: <span class="token number">0</span>  <span class="token keyword">Warnings</span>: <span class="token number">0</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="_5-综合案例"><a href="#_5-综合案例" class="header-anchor">#</a> 5. 综合案例</h2> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 1、创建数据库test01_library</span>

<span class="token comment"># 2、创建表 books，表结构如下：</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929555.png" alt="image-20220610122253765"></p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 3、向books表中插入记录</span>
<span class="token comment"># 1）不指定字段名称，插入第一条记录</span>

<span class="token comment"># 2）指定所有字段名称，插入第二记录</span>

<span class="token comment"># 3）同时插入多条记录（剩下的所有记录）</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207251929556.png" alt="image-20220610122317952"></p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 4、将小说类型(novel)的书的价格都增加5。</span>

<span class="token comment"># 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。</span>

<span class="token comment"># 6、删除库存为0的记录。</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 7、统计书名中包含a字母的书</span>

<span class="token comment"># 8、统计书名中包含a字母的书的数量和库存总量</span>

<span class="token comment"># 9、找出“novel”类型的书，按照价格降序排列</span>

<span class="token comment"># 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列</span>

<span class="token comment"># 11、按照note分类统计书的数量</span>

<span class="token comment"># 12、按照note分类统计书的库存量，显示库存量超过30本的</span>

<span class="token comment"># 13、查询所有图书，每页显示5本，显示第二页</span>

<span class="token comment"># 14、按照note分类统计书的库存量，显示库存量最多的</span>

<span class="token comment"># 15、查询书名达到10个字符的书，不包括里面的空格</span>

<span class="token comment"># 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话</span>

<span class="token comment"># 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货</span>

<span class="token comment"># 18、统计每一种note的库存量，并合计总量</span>

<span class="token comment"># 19、统计每一种note的数量，并合计总量</span>

<span class="token comment"># 20、统计库存量前三名的图书</span>

<span class="token comment"># 21、找出最早出版的一本书</span>

<span class="token comment"># 22、找出novel中价格最高的一本书</span>

<span class="token comment"># 23、找出书名中字数最多的一本书，不含空格</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br></div></div><p>答案：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#1、创建数据库test01_library</span>
<span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> test01_library <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token string">'utf8'</span><span class="token punctuation">;</span>

<span class="token comment">#指定使用哪个数据库</span>
<span class="token keyword">USE</span> test01_library<span class="token punctuation">;</span>

<span class="token comment">#2、创建表 books</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> books<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span><span class="token punctuation">,</span>
    name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>authors<span class="token punctuation">`</span></span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token punctuation">,</span>
    price <span class="token keyword">FLOAT</span><span class="token punctuation">,</span>
    pubdate <span class="token keyword">YEAR</span> <span class="token punctuation">,</span>
    note <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    num <span class="token keyword">INT</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">#3、向books表中插入记录</span>
<span class="token comment"># 1）不指定字段名称，插入第一条记录</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> books
<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'Tal of AAA'</span><span class="token punctuation">,</span><span class="token string">'Dickes'</span><span class="token punctuation">,</span><span class="token number">23</span><span class="token punctuation">,</span><span class="token number">1995</span><span class="token punctuation">,</span><span class="token string">'novel'</span><span class="token punctuation">,</span><span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 2）指定所有字段名称，插入第二记录</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> books <span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">,</span><span class="token identifier"><span class="token punctuation">`</span>authors<span class="token punctuation">`</span></span><span class="token punctuation">,</span>price<span class="token punctuation">,</span>pubdate<span class="token punctuation">,</span>note<span class="token punctuation">,</span>num<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'EmmaT'</span><span class="token punctuation">,</span><span class="token string">'Jane lura'</span><span class="token punctuation">,</span><span class="token number">35</span><span class="token punctuation">,</span><span class="token number">1993</span><span class="token punctuation">,</span><span class="token string">'Joke'</span><span class="token punctuation">,</span><span class="token number">22</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 3）同时插入多条记录（剩下的所有记录）</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> books <span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">,</span><span class="token identifier"><span class="token punctuation">`</span>authors<span class="token punctuation">`</span></span><span class="token punctuation">,</span>price<span class="token punctuation">,</span>pubdate<span class="token punctuation">,</span>note<span class="token punctuation">,</span>num<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
<span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'Story of Jane'</span><span class="token punctuation">,</span><span class="token string">'Jane Tim'</span><span class="token punctuation">,</span><span class="token number">40</span><span class="token punctuation">,</span><span class="token number">2001</span><span class="token punctuation">,</span><span class="token string">'novel'</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'Lovey Day'</span><span class="token punctuation">,</span><span class="token string">'George Byron'</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">2005</span><span class="token punctuation">,</span><span class="token string">'novel'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token string">'Old land'</span><span class="token punctuation">,</span><span class="token string">'Honore Blade'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token number">2010</span><span class="token punctuation">,</span><span class="token string">'Law'</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span><span class="token string">'The Battle'</span><span class="token punctuation">,</span><span class="token string">'Upton Sara'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token number">1999</span><span class="token punctuation">,</span><span class="token string">'medicine'</span><span class="token punctuation">,</span><span class="token number">40</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token string">'Rose Hood'</span><span class="token punctuation">,</span><span class="token string">'Richard haggard'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">,</span><span class="token number">2008</span><span class="token punctuation">,</span><span class="token string">'cartoon'</span><span class="token punctuation">,</span><span class="token number">28</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 4、将小说类型(novel)的书的价格都增加5。</span>
<span class="token keyword">UPDATE</span> books <span class="token keyword">SET</span> price<span class="token operator">=</span>price<span class="token operator">+</span><span class="token number">5</span> <span class="token keyword">WHERE</span> note <span class="token operator">=</span> <span class="token string">'novel'</span><span class="token punctuation">;</span>

<span class="token comment"># 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。</span>
<span class="token keyword">UPDATE</span> books <span class="token keyword">SET</span> price<span class="token operator">=</span><span class="token number">40</span><span class="token punctuation">,</span>note<span class="token operator">=</span><span class="token string">'drama'</span> <span class="token keyword">WHERE</span> name<span class="token operator">=</span><span class="token string">'EmmaT'</span><span class="token punctuation">;</span>

<span class="token comment"># 6、删除库存为0的记录。</span>
<span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> books <span class="token keyword">WHERE</span> num<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>

<span class="token comment"># 7、统计书名中包含a字母的书</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">WHERE</span> name <span class="token operator">LIKE</span> <span class="token string">'%a%'</span><span class="token punctuation">;</span>

<span class="token comment"># 8、统计书名中包含a字母的书的数量和库存总量</span>
<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>num<span class="token punctuation">)</span> <span class="token keyword">FROM</span> books <span class="token keyword">WHERE</span> name <span class="token operator">LIKE</span> <span class="token string">'%a%'</span><span class="token punctuation">;</span>

<span class="token comment"># 9、找出“novel”类型的书，按照价格降序排列</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">WHERE</span> note <span class="token operator">=</span> <span class="token string">'novel'</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> price <span class="token keyword">DESC</span><span class="token punctuation">;</span>

<span class="token comment"># 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> num <span class="token keyword">DESC</span><span class="token punctuation">,</span>note <span class="token keyword">ASC</span><span class="token punctuation">;</span>

<span class="token comment"># 11、按照note分类统计书的数量</span>
<span class="token keyword">SELECT</span> note<span class="token punctuation">,</span><span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> books <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> note<span class="token punctuation">;</span>

<span class="token comment"># 12、按照note分类统计书的库存量，显示库存量超过30本的</span>
<span class="token keyword">SELECT</span> note<span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>num<span class="token punctuation">)</span> <span class="token keyword">FROM</span> books <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> note <span class="token keyword">HAVING</span> <span class="token function">SUM</span><span class="token punctuation">(</span>num<span class="token punctuation">)</span><span class="token operator">&gt;</span><span class="token number">30</span><span class="token punctuation">;</span>

<span class="token comment"># 13、查询所有图书，每页显示5本，显示第二页</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">;</span>

<span class="token comment"># 14、按照note分类统计书的库存量，显示库存量最多的</span>
<span class="token keyword">SELECT</span> note<span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>num<span class="token punctuation">)</span> sum_num <span class="token keyword">FROM</span> books <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> note <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> sum_num <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment"># 15、查询书名达到10个字符的书，不包括里面的空格</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">WHERE</span> CHAR_LENGTH<span class="token punctuation">(</span><span class="token keyword">REPLACE</span><span class="token punctuation">(</span>name<span class="token punctuation">,</span><span class="token string">' '</span><span class="token punctuation">,</span><span class="token string">''</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">&gt;=</span><span class="token number">10</span><span class="token punctuation">;</span>

<span class="token comment"># 16、查询书名和类型，其中note值为 novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话</span>
<span class="token keyword">SELECT</span> name <span class="token keyword">AS</span> <span class="token string">&quot;书名&quot;</span> <span class="token punctuation">,</span>note<span class="token punctuation">,</span> <span class="token keyword">CASE</span> note
<span class="token keyword">WHEN</span> <span class="token string">'novel'</span> <span class="token keyword">THEN</span> <span class="token string">'小说'</span>
<span class="token keyword">WHEN</span> <span class="token string">'law'</span> <span class="token keyword">THEN</span> <span class="token string">'法律'</span>
<span class="token keyword">WHEN</span> <span class="token string">'medicine'</span> <span class="token keyword">THEN</span> <span class="token string">'医药'</span>
<span class="token keyword">WHEN</span> <span class="token string">'cartoon'</span> <span class="token keyword">THEN</span> <span class="token string">'卡通'</span>
<span class="token keyword">WHEN</span> <span class="token string">'joke'</span> <span class="token keyword">THEN</span> <span class="token string">'笑话'</span>
<span class="token keyword">END</span> <span class="token keyword">AS</span> <span class="token string">&quot;类型&quot;</span>
<span class="token keyword">FROM</span> books<span class="token punctuation">;</span>

<span class="token comment"># 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>num<span class="token punctuation">,</span><span class="token keyword">CASE</span>
 <span class="token keyword">WHEN</span> num<span class="token operator">&gt;</span><span class="token number">30</span> <span class="token keyword">THEN</span> <span class="token string">'滞销'</span>
 <span class="token keyword">WHEN</span> num<span class="token operator">&gt;</span><span class="token number">0</span> <span class="token operator">AND</span> num<span class="token operator">&lt;</span><span class="token number">10</span> <span class="token keyword">THEN</span> <span class="token string">'畅销'</span>
 <span class="token keyword">WHEN</span> num<span class="token operator">=</span><span class="token number">0</span> <span class="token keyword">THEN</span> <span class="token string">'无货'</span>
 <span class="token keyword">ELSE</span> <span class="token string">'正常'</span>
 <span class="token keyword">END</span> <span class="token keyword">AS</span> <span class="token string">&quot;库存状态&quot;</span>
<span class="token keyword">FROM</span> books<span class="token punctuation">;</span>

<span class="token comment"># 18、统计每一种note的库存量，并合计总量</span>
<span class="token keyword">SELECT</span> IFNULL<span class="token punctuation">(</span>note<span class="token punctuation">,</span><span class="token string">'合计总库存量'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> note<span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>num<span class="token punctuation">)</span> <span class="token keyword">FROM</span> books <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> note <span class="token keyword">WITH ROLLUP</span><span class="token punctuation">;</span>

<span class="token comment"># 19、统计每一种note的数量，并合计总量</span>
<span class="token keyword">SELECT</span> IFNULL<span class="token punctuation">(</span>note<span class="token punctuation">,</span><span class="token string">'合计总数'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> note<span class="token punctuation">,</span><span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> books <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> note <span class="token keyword">WITH ROLLUP</span><span class="token punctuation">;</span>

<span class="token comment"># 20、统计库存量前三名的图书</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> num <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">;</span>

<span class="token comment"># 21、找出最早出版的一本书</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> pubdate <span class="token keyword">ASC</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment"># 22、找出novel中价格最高的一本书</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">WHERE</span> note <span class="token operator">=</span> <span class="token string">'novel'</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> price <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment"># 23、找出书名中字数最多的一本书，不含空格</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> books <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> CHAR_LENGTH<span class="token punctuation">(</span><span class="token keyword">REPLACE</span><span class="token punctuation">(</span>name<span class="token punctuation">,</span><span class="token string">' '</span><span class="token punctuation">,</span><span class="token string">''</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br><span class="line-number">83</span><br><span class="line-number">84</span><br><span class="line-number">85</span><br><span class="line-number">86</span><br><span class="line-number">87</span><br><span class="line-number">88</span><br><span class="line-number">89</span><br><span class="line-number">90</span><br><span class="line-number">91</span><br><span class="line-number">92</span><br><span class="line-number">93</span><br><span class="line-number">94</span><br><span class="line-number">95</span><br><span class="line-number">96</span><br><span class="line-number">97</span><br><span class="line-number">98</span><br><span class="line-number">99</span><br><span class="line-number">100</span><br><span class="line-number">101</span><br><span class="line-number">102</span><br><span class="line-number">103</span><br><span class="line-number">104</span><br><span class="line-number">105</span><br><span class="line-number">106</span><br></div></div></div></div> <!----> <div class="page-edit"><div class="edit-link"><a href="https://github.com/lcfqzd/vuepress-theme-vdoing/edit/master/docs/04.数据库/01.MySQL笔记/11.数据处理之增删改.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="tags"><a href="/noteslibrary/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/noteslibrary/pages/70a90b/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">创建和管理表</div></a> <a href="/noteslibrary/pages/9515c0/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">MySQL数据类型精讲</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/noteslibrary/pages/70a90b/" class="prev">创建和管理表</a></span> <span class="next"><a href="/noteslibrary/pages/9515c0/">MySQL数据类型精讲</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2018-2023
    <span>LCFQZD | MIT License</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"></div></div>
    <script src="/noteslibrary/assets/js/app.dc0c3d24.js" defer></script><script src="/noteslibrary/assets/js/2.a664539c.js" defer></script><script src="/noteslibrary/assets/js/98.e9a01f98.js" defer></script>
  </body>
</html>
